<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

class ZExamRule{

    public $conn;
    public function __construct() {
        $this->conn = Yii::app()->db;
    }
    
    /**
     * 得到科目等级规则
     * 
     * @version 1.0
     * @date 2013-12-18
     * @author jinyue.wang
     */
    public function _getGrading(){
        
        $data = array();
        
        $sql = ' SELECT "id","grading_name" FROM "{{grading}}" ';
        $result = $this->conn->createCommand($sql);
        $row = $result->queryAll();
        if(!empty($row)){
            foreach($row as $value){
                $data[$value['id']] = $value['grading_name'];
            }
        }
        
        return $data;
    }
    /**
     * 得到 C对应的id
     * 
     * @version 1.0
     * @date 2014-01-23
     */
    public function _getGradingCpoint(){
        $data = array();
        $grading_id = 0;
        $sql = 'SELECT "id","grading_name" FROM "{{grading}}" WHERE "grading_name" = \'C\' OR "grading_name" = \'c\' ';
        $result = $this->conn->createCommand($sql);
        $row = $result->queryRow();
        if(!empty($row)){
            $grading_id = $row['id'];
        }
        return $grading_id;
    }
    public function _getGradingBpoint(){
        $data = array();
        $grading_id = 0;
        $sql = 'SELECT "id","grading_name" FROM "{{grading}}" WHERE "grading_name" = \'B\' OR "grading_name" = \'b\' ';
        $result = $this->conn->createCommand($sql);
        $row = $result->queryRow();
        if(!empty($row)){
            $grading_id = $row['id'];
        }
        return $grading_id;
    }
    
    /**
     * 得到当前考试等级的类型
     * 
     * @param $exam_id
     * 
     * @return array
     * 
     * @version 1.0
     * @author jinyue.wang
     * @date 2013-12-18
     */
    public function _getExamTypeArray($exam_id){
        
        $new_data = array();
        if($exam_id>0){
            $sql = 'SELECT "grading_id","subject_id","score" FROM "{{point_grading}}" WHERE "exam_id" = :exam_id ';
            $result = $this->conn->createCommand($sql);
            $result->bindParam(':exam_id',$exam_id,PDO::PARAM_STR);
            
            $data = $result->queryAll();
            if(!empty($data)){
                foreach($data as $value){
                    $new_data[$value['subject_id']][] = $value;
                }
            }
        }
        return $new_data;
    }
    public function _getExamGarding($exam_id,$garding_id,$subject_array){
        $new_data = array();
        if($exam_id>0 && $garding_id>0){
            $sql = 'SELECT "grading_id","subject_id","score" FROM "{{point_grading}}" WHERE "exam_id" = :exam_id AND "grading_id" = :grading_id ';
            $result = $this->conn->createCommand($sql);
            $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
            $result->bindParam(':grading_id', $garding_id, PDO::PARAM_STR);
            $data = $result->queryAll();
            if (!empty($data)) {
                foreach ($data as $value) {
                    $subject_code = isset($subject_array[$value['subject_id']]['subject_code']) ? $subject_array[$value['subject_id']]['subject_code'] : '';
                    if (!empty($subject_code)) {
                        $new_data[$subject_code.'f'] = $value['score'];
                    }
                }
            }
        }
        
        return $new_data;
    }
    
    /**
     * 得到考试成绩
     * 
     * @version 1.0
     * @date 2013-12-18
     * @author jinyue.wang
     */
    public function _getExamArray($exam_id,$school_code){
        
        if(empty($exam_id) || empty($school_code))
            return null;
        
        $new_data = $return_data = $student_data = array();
        
        $sql = 'SELECT "exam_id","student_id","subject_id","disciplin_type","score" FROM "{{point}}" WHERE "exam_id" = :exam_id AND "school_code" = :school_code ';

        $result = $this->conn->createCommand($sql);
        $result->bindParam(':exam_id',$exam_id,PDO::PARAM_STR);
        $result->bindParam(':school_code',$school_code,PDO::PARAM_STR);
        
        $data = $result->queryAll();
        if(!empty($data)){
            foreach($data as $value){
                if(!isset($new_data[$value['disciplin_type']][$value['student_id']]))
                    $new_data[$value['disciplin_type']][$value['student_id']] = 0;
                
                 $new_data[$value['disciplin_type']][$value['student_id']] += $value['score'];
                 $student_data[$value['student_id']][$value['subject_id']] = $value['score'];
            }
        }
        $result_data['data'] = $new_data;
        $result_data['point'] = $student_data;
        
        return $result_data;
    }
    /**
     * 统计开始
     * 
     * 得到科目的考试成绩
     * 
     * @author jinyue.wang
     * @version 1.0
     * @date 2014-01-22
     */
    public function _getExamPoint($exam_id, $school_code = '') {
        if (empty($exam_id))
            return null;
        $data = array();
        if (!empty($school_code)) {
            $sql = 'SELECT "student_id","student_name","exam_id","school_id","school_code","school_name","disciplin_type","disciplin_code","ywf","sxf","yyf","wlf","hxf","swf","zzf","dlf","lsf","socre" FROM "{{student}}" WHERE "exam_id" = :exam_id AND "school_code" = :school_code AND "statis" = \'T\'';
            $result = $this->conn->createCommand($sql);
            $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
            $result->bindParam(':school_code',$school_code,PDO::PARAM_STR);
            $data = $result->queryAll();
        } else {
            $sql = 'SELECT "student_id","student_name","exam_id","school_id","school_code","school_name","disciplin_type","disciplin_code","ywf","sxf","yyf","wlf","hxf","swf","zzf","dlf","lsf","socre" FROM "{{student}}" WHERE "exam_id" = :exam_id AND "statis" = \'T\'';
            $result = $this->conn->createCommand($sql);
            $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
            $data = $result->queryAll();
        }
        return $data;
    }
    public function _getExamPointNewArr($point_array) {
        $new_data = array();
        if (!empty($point_array)) {
            foreach ($point_array as $key => $value) {
                $point = $value['ywf'] + $value['sxf'] + $value['yyf'];
                $value['zf'] = $point;
                $new_data[$value['disciplin_type']][] = $value;
            }
        }
        return $new_data;
    }
    public function _getExamPointArray($point_array) {
        $new_data = $student_data = $result_data = array();
        if (!empty($point_array)) {
            foreach ($point_array as $key => $value) {
                if (!isset($new_data[$value['disciplin_type']][$value['student_id']]))
                    $new_data[$value['disciplin_type']][$value['student_id']] = 0;
                $point = 0;
                $point = $value['ywf'] + $value['sxf'] + $value['yyf'];
                $new_data[$value['disciplin_type']][$value['student_id']] = $point;
                $student_data[$value['student_id']] = $value;
            }
            $result_data['data'] = $new_data;
            $result_data['point'] = $student_data;
            return $result_data;
        }
    }
    /**
     *  得到在籍人数的信息
     * 
     *  @author jinyue.wang
     *  @date 2014-02-27
     */
    public function _getMeshipArray($mt_id,$school_id){
        $mdata = array();
        if($mt_id>0 && $school_id >0){
            $sql = "SELECT \"mt_id\",\"mt_num\" FROM \"{{membership_num}}\" WHERE \"membership_id\" = ".$mt_id." AND \"school_id\" = ".$school_id;
            $result = $this->conn->createcommand($sql);
            $data = $result->queryAll();
            if(!empty($data)){
                foreach($data as $value){
                    $mdata[$value['mt_id']] = $value['mt_num'];
                }
            }
        }
        return $mdata;
    }
}

?>
